package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.dao.SaleOrderDAO;
import com.model.SaleOrder;
import com.util.DB;

public class SaleOrderDAOImpl implements SaleOrderDAO {
	// private Connection conn = DB.getConnection();
	private Connection conn = DB.con;

	// 记录订单
	@Override
	public boolean addOrder(SaleOrder saleOrder) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			String saleOrderNo = saleOrder.getSaleOrderNo();
			int customer_Id = saleOrder.getCustomer_Id();
			double total = saleOrder.getTotal();
			Date createDateTime = saleOrder.getCreateDateTime();
			int state = saleOrder.getState();

			st = conn
					.prepareStatement("insert into saleOrder(SALEORDERNO,CUSTOMER_ID,TOTAL,CREATEDATETIME,STATE) values(?,?,?,now(),?)");
			st.setString(1, saleOrderNo);
			st.setInt(2, customer_Id);
			st.setDouble(3, total);
			st.setInt(4, state);

			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	// 查询订单
	@Override
	public List<SaleOrder> selectSaleOrder(String key, String type, int page,
			int rows, String sort, String order) {
		// TODO Auto-generated method stub
		List<SaleOrder> list = new ArrayList<SaleOrder>();
		PreparedStatement st = null;
		int startRows = (page - 1) * rows;
		int endRows = rows;
		try {
			if (key != null) {
				String sql = "select * from saleorder where id like ? order by saleorderno ASC limit ?,?";
				sql = sql.replace("id", type);
				sql = sql.replace("saleorderno", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setString(1, "%" + key + "%");
				st.setInt(2, startRows);
				st.setInt(3, endRows);
				ResultSet rs = st.executeQuery();
				// 循环取出数据
				while (rs.next()) {
					SaleOrder saleOrder = new SaleOrder();
					saleOrder.setId(rs.getInt("id"));
					saleOrder.setSaleOrderNo(rs.getString("saleorderno"));
					saleOrder.setCustomer_Id(rs.getInt("customer_id"));
					saleOrder.setCreateDateTime(rs.getDate("createDateTime"));
					saleOrder.setTotal(rs.getDouble("total"));
					saleOrder.setState(rs.getInt("state"));
					list.add(saleOrder);
				}
				return list;
			} else {
				String sql = "select * from saleorder order by id ASC limit ?,?";
				sql = sql.replace("id", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setInt(1, startRows);
				st.setInt(2, endRows);
				ResultSet rs = st.executeQuery();
				while (rs.next()) {
					SaleOrder saleOrder = new SaleOrder();
					saleOrder.setId(rs.getInt("id"));
					saleOrder.setSaleOrderNo(rs.getString("saleorderno"));
					saleOrder.setCustomer_Id(rs.getInt("customer_id"));
					saleOrder.setCreateDateTime(rs.getDate("createDateTime"));
					saleOrder.setTotal(rs.getDouble("total"));
					saleOrder.setState(rs.getInt("state"));
					list.add(saleOrder);
				}
				return list;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return null;
	}

	// 获取SaleOrder数量
	public int getTotal() {
		PreparedStatement st = null;
		int count = 0;
		try {
			st = conn.prepareStatement("select id from saleorder");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	// 获取查询目标的数量
	public int getLikeTotal(String key, String type) {
		PreparedStatement st = null;
		int count = 0;
		try {
			String sql = "select * from saleOrder where id like ?";
			sql = sql.replace("id", type);
			st = conn.prepareStatement(sql);
			st.setString(1, "%" + key + "%");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	// 取出最后的记录id
	@Override
	public int getLastNum() {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			int lastNum = 0;
			st = conn
					.prepareStatement("select id from saleorder where id = (select max(id) from saleorder)");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				lastNum = rs.getInt("id");
			}
			return lastNum;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

}
